package com.ebrothers.isns.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.Logger;

public abstract class GenericDAO {
	private static final Logger logger = Logger.getLogger(GenericDAO.class);
	private static DBConnector connector;
	private Map<ResultSet, PreparedStatement> statements;
	private Map<ResultSet, Connection> connections;

	public GenericDAO() {
		try {
			connector = DBConnector
					.getInstance(
							"jdbc:mysql://localhost/isns1?autoReconnect=true&useUnicode=true&characterEncoding=gbk",
							"isns1", "zaq12wsx");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		statements = new HashMap<ResultSet, PreparedStatement>();
		connections = new HashMap<ResultSet, Connection>();
	}

	private Connection getConnection() throws DAOException {
		try {
			return connector.getConnection();
		} catch (SQLException e) {
			logger.error("GOT AN ERROR!", e);
			throw new DAOException(e);
		}
	}

	private void releaseConnection(Connection connection) throws DAOException {
		try {
			connection.close();
		} catch (SQLException e) {
			logger.error("GOT AN ERROR!", e);
			throw new DAOException(e);
		}
	}

	protected void commit(Connection connection) throws DAOException {
		try {
			connection.commit();
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}

	protected void rollback(Connection connection) throws DAOException {
		try {
			connection.rollback();
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}

	public int insert(String sql) throws DAOException {
		logger.info("[insert]: " + sql);
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			int rows = ps.executeUpdate();
			return rows;
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closePreparedStatement(ps);
			releaseConnection(connection);
		}
	}

	public ResultSet query(String sql) throws DAOException {
		logger.info("[query]: " + sql);
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			statements.put(rs, ps);
			connections.put(rs, connection);
			return rs;
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}

	public long getIdByIdentify(String table, String identifyCol,
			String identifyVal) throws DAOException {
		String sql = "select id from " + table + " where " + identifyCol + "='"
				+ identifyVal + "';";
		ResultSet rs = query(sql);
		try {
			if (rs.next()) {
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closeResultSet(rs);
		}
		return -1;
	}

	public int getIdByIdentify(String table, String[] identifyCols,
			String[] identifyVals) throws DAOException {
		if (identifyCols.length != identifyVals.length) {
			throw new DAOException(
					"[getIdByIdentify]: Columns count is not consistent with values count. ");
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select id from ");
		sb.append(table);
		sb.append(" where ");
		for (int i = 0; i < identifyCols.length; i++) {
			sb.append(identifyCols[i]);
			sb.append("='");
			sb.append(identifyVals[i]);
			if (i < identifyCols.length - 1) {
				sb.append("' AND ");
			} else {
				sb.append("';");
			}
		}
		String sql = sb.toString();
		ResultSet rs = query(sql);
		try {
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closeResultSet(rs);
		}
		return -1;
	}

	public boolean isExist(String table, String identifyCol, String identifyVal)
			throws DAOException {
		String sql = "select id from " + table + " where " + identifyCol + "='"
				+ identifyVal + "';";
		ResultSet rs = query(sql);
		try {
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closeResultSet(rs);
		}
		return false;
	}

	public boolean isExist(String table, String[] identifyCols,
			String[] identifyVals) throws DAOException {
		if (identifyCols.length != identifyVals.length) {
			throw new DAOException(
					"[isExist]: Columns count is not consistent with values count. ");
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select id from ");
		sb.append(table);
		sb.append(" where ");
		for (int i = 0; i < identifyCols.length; i++) {
			sb.append(identifyCols[i]);
			sb.append("='");
			sb.append(identifyVals[i]);
			if (i < identifyCols.length - 1) {
				sb.append("' AND ");
			} else {
				sb.append("';");
			}
		}
		String sql = sb.toString();
		ResultSet rs = query(sql);
		try {
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closeResultSet(rs);
		}
		return false;
	}

	public void closePreparedStatement(PreparedStatement ps)
			throws DAOException {
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				throw new DAOException(e);
			}
		}
	}

	public void closeResultSet(ResultSet rs) throws DAOException {
		if (rs != null) {
			if (statements.containsKey(rs)) {
				closePreparedStatement(statements.get(rs));
				statements.remove(rs);
			}
			try {
				rs.close();
			} catch (SQLException e) {
				throw new DAOException(e);
			}
			if (connections.containsKey(rs)) {
				releaseConnection(connections.get(rs));
				connections.remove(rs);
			}
		}
	}

}
